import pandas as pd
import numpy as np

def equal_split(total, n):
    if n == 0:
        return []
    base = round(total / n, 2)
    return [base] * n

def ensure_sum_fix(values, target_sum):
    values = [round(v, 2) for v in values]
    current_sum = round(sum(values), 2)
    diff = round(target_sum - current_sum, 2)
    
    if abs(diff) < 0.01:
        return values
    
    n = len(values)
    adjustment = 0.01 if diff > 0 else -0.01
    adjusted_values = values.copy()
    
    for i in range(int(abs(diff * 100))):
        adjusted_values[i % n] = round(adjusted_values[i % n] + adjustment, 2)
    
    return adjusted_values

INPUT_PATH = "runs\\20251004-075348\\input.xlsx"
df = pd.read_excel(INPUT_PATH, sheet_name='退货明细', engine='openpyxl')

person_stats = df.groupby('Person').agg(
    Items=('Item', 'count'),
    TotalAmount=('Amount', 'sum')
).reset_index()

total_order_amount = df['Amount'].sum()
num_people = len(person_stats)
equal_shares = equal_split(total_order_amount, num_people)
person_stats['ShareAmount'] = ensure_sum_fix(equal_shares, total_order_amount)

person_stats = person_stats[['Person', 'Items', 'ShareAmount']]
person_stats['ShareAmount'] = person_stats['ShareAmount'].round(2)

with pd.ExcelWriter(INPUT_PATH, mode='a', if_sheet_exists='replace', engine='openpyxl') as writer:
    person_stats.to_excel(writer, sheet_name='Result', index=False)

print("DONE: Person refund stats with equal share amounts")